import pickle
import sqlite3


class FileDBStrategy:
    def save(self, file, obj):
        try:
            with open(file, "wb") as f:
                pickle.dump(obj, f)
        except Exception as e:
            print(e.args[0])

    def load(self, file):
        try:
            with open(file, "rb") as f:
                return pickle.load(f)
        except Exception as e:
            print(e.args[0])


class SQLiteDBStrategy:
    def __init__(self, sql_path):
        self.sql_path = sql_path
        self.open()
        self.sql.execute(
            """
           create table if not exists Shop
           (
               inventory_number integer primary key autoincrement,
               model text,
               year int,
               capacity int
           );
        """
        )
        self.close()

    def open(self):
        self.sql = sqlite3.connect(
            self.sql_path, detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread=False
        )
        self.sql.row_factory = sqlite3.Row
        self.sqlc = self.sql.cursor()

    def store(self):
        self.sql.commit()

    def close(self):
        self.sql.close()

    def add(self, car, type_of_c):
        self.open()
        if type_of_c == "car":
            self.sql.execute(
                "insert into Shop values(NULL, ?, ?, ?)", (car.model, car.year, 0)
            )
            print("sql car")
        elif type_of_c == "truck":
            self.sql.execute(
                "insert into Shop values(NULL, ?, ?, ?)",
                (car.model, car.year, car.capacity),
            )
            print("sql truck")
        self.store()
        self.close()

    def change(self, car, type_of_c):
        self.open()
        if type_of_c == "car":
            self.sql.execute(
                "update Shop set model=?, year=?, capacity=?", (car.model, car.year, 0)
            )
        elif type_of_c == "truck":
            self.sql.execute(
                "update Shop set model=?, year=?, capacity=?",
                (car.model, car.year, car.capacity),
            )
        self.store()
        self.close()

    def delete(self, num):
        self.open()
        self.sql.execute("delete from Shop where inventory_number=?", num)

        self.store()
        self.close()

    def clear(self):
        self.open()
        self.sql.execute("drop table Shop")
        self.sql.commit()
        self.store()
        self.close()

    def get_items(self):
        self.open()
        self.sqlc.execute("select * from Shop order by inventory_number desc")
        query_items = list()
        for r in self.sqlc:
            if not r["capacity"]:
                data = {
                    "type": "car",
                    "model": r[1],
                    "year": r[2],
                }
                query_items.append(data)
            else:
                data = {
                    "type": "truck",
                    "model": r[1],
                    "year": r[2],
                    "capacity": r[3],
                }
                query_items.append(data)
        self.close()
        return query_items
